﻿using System;
using System.Data.SqlClient;
using sscore;
using sscore.Utils;
using System.Data;

namespace DM4
{
    public partial class DM4
    {
        public MailSendTemplate GetMailSendTemplateInfo(string name)
        {
            return GetMailSendTemplateInfo(-1, name);
        }

        public MailSendTemplate GetMailSendTemplateInfo(int id, string name = "")
        {
            BeginFunction("MailSendTemplate");

            MailSendTemplate si = new MailSendTemplate();

            string sql = name == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailSendTemplate, CnMailSendTemplate.Id, id)
                : String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}'", Tn.MailSendTemplate, CnMailSendTemplate.Name, name.Trim().ToUpper());

            WriteDebugLog(sql);

            using (SqlCommand sel = new SqlCommand(sql, _conn))
            {
                using (SqlDataReader reader = sel.ExecuteReader())
                {
                    if (reader.HasRows)
                        try
                        {
                            reader.Read();
                            si.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailSendTemplate.Id]);
                            si.Name = SysUtils.ConvertFromDBVal<string>(reader[CnMailSendTemplate.Name]);
                            si.Subject = SysUtils.ConvertFromDBVal<string>(reader[CnMailSendTemplate.Subject]);
                            si.Body = SysUtils.ConvertFromDBVal<string>(reader[CnMailSendTemplate.Body]);
                        }
                        catch (Exception ex)
                        {
                            WriteLog(String.Format("Error occurred while GetMailSendTemplateInfo: Id [{0}], name [{1}] {2}", id, name, ex.Message), MessageType.Error);
                        }
                }
            }
            EndFunction("MailSendTemplate");
            return si;
        }

        public XMLInfo GetMailSendTemplateList(string usrSessionId)
        {
            BeginFunction("GetMailSendTemplateList");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            XMLInfo xi = new XMLInfo(userIdOpResult);

            if (!userIdOpResult)
            {
                WriteLog(userIdOpResult.Message, MessageType.Error);
                return xi;
            }

            const string sql = "select * from " + Tn.MailSendTemplate;

            WriteDebugLog(sql);

            try
            {

                using (SqlCommand sel = new SqlCommand(sql, _conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {

                        try
                        {
                            WriteLog("Get send mail templates list operation complete");
                            xi.Load(reader, "templates");
                            //WriteDebugLog(xi.XSD);
                        }

                        catch (Exception ex)
                        {
                            WriteLog(ex.Message, MessageType.Error);
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("GetMailSendTemplateList");
            return xi;
        }

        public OperationResult AddMailSendTemplate(string usrSessionId, string name, string subject, string body)
        {
            BeginFunction("AddMailSendTemplate");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog("Attempt to add mail template...");
            try
            {
                MailSendTemplate smti = GetMailSendTemplateInfo(name);

                if (String.IsNullOrEmpty(smti.Name))
                {
                    string sql = String.Format("insert  into  {0} ([{1}],[{2}],[{3}]) "
                        + " values ('{4}', @{2}, @{3})"
                        , Tn.MailSendTemplate
                        , CnMailSendTemplate.Name, CnMailSendTemplate.Subject, CnMailSendTemplate.Body
                        , name);

                    WriteDebugLog(sql);

                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        SqlParameter paramSubject = sc.Parameters.Add("@" + CnMailSendTemplate.Subject, SqlDbType.NVarChar);
                        paramSubject.Value = subject;
                        paramSubject.Size = -1;

                        SqlParameter paramBody = sc.Parameters.Add("@" + CnMailSendTemplate.Body, SqlDbType.NVarChar);
                        paramBody.Value = body;
                        paramBody.Size = -1;

                        sc.ExecuteNonQuery();
                    }
                    smti = GetMailSendTemplateInfo(name);

                    result.Result = smti.Id;
                    result.Message = String.Format("Mail template with name [{0}] added", name);
                    WriteLog(result.Message);
                }
                else
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("Mail template with name [{0}] already exists", name);
                    WriteLog(result.Message, MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("AddMailSendTemplate");
            return result;
        }

        public OperationResult EditMailSendTemplate(string usrSessionId, int templateId, string name, string subject, string body)
        {
            BeginFunction("EditMailSendTemplate");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to edit mail template with id [{0}]...", templateId));
            try
            {
                MailSendTemplate check = GetMailSendTemplateInfo(name);
                if ((!String.IsNullOrEmpty(check.Name)) && (check.Id != templateId))
                {
                    result.Result = DM4Consts.ErrorEntityWithSomeNameAlreadyExists;
                    result.Message = String.Format("Mail template with name [{0}] already exists", name);
                    WriteLog(result.Message, MessageType.Error);
                }
                else
                {
                    MailSendTemplate msti = GetMailSendTemplateInfo(templateId);
                    if (!String.IsNullOrEmpty(msti.Name))
                    {
                        string sql = String.Format("update [{0}] set [{1}]='{2}', [{3}] = @{3}, [{4}] = @{4} where [{5}] = {6}",
                            Tn.MailSendTemplate
                            , CnMailSendTemplate.Name, name
                            , CnMailSendTemplate.Subject
                            , CnMailSendTemplate.Body
                            , CnMailSendTemplate.Id, templateId);

                        WriteDebugLog(sql);
                        using (SqlCommand sc = new SqlCommand(sql, _conn))
                        {
                            SqlParameter paramSubject = sc.Parameters.Add("@" + CnMailSendTemplate.Subject, SqlDbType.NVarChar);
                            paramSubject.Value = subject;
                            paramSubject.Size = -1;

                            SqlParameter paramBody = sc.Parameters.Add("@" + CnMailSendTemplate.Body, SqlDbType.NVarChar);
                            paramBody.Value = body;
                            paramBody.Size = -1;
                            sc.ExecuteNonQuery();
                        }

                        msti = GetMailSendTemplateInfo(templateId);

                        result.Result = msti.Id;
                        result.Message = String.Format("Mail template with id [{0}] modified", templateId);
                        WriteLog(result.Message);

                    }
                    else
                    {
                        result.Result = DM4Consts.ErrorUnknownEntity;
                        result.Message = String.Format("Unknown mail template with id [{0}]", templateId);
                        WriteLog(result.Message, MessageType.Error);
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("EditMailSendTemplate");
            return result;
        }

        public OperationResult DeleteMailSendTemplate(string usrSessionId, int templateId)
        {
            BeginFunction("DeleteMailSendTemplate");

            OperationResult userIdOpResult = GetUserIdBySession(usrSessionId);
            if (!userIdOpResult) return userIdOpResult;

            OperationResult result = new OperationResult(userIdOpResult, DM4Consts.ErrorUnknown);

            WriteLog(String.Format("Attempt to delete mail template [{0}]...", templateId));
            try
            {
                MailSendTemplate msti = GetMailSendTemplateInfo(templateId);

                if (!String.IsNullOrEmpty(msti.Name))
                {
                    string sql = String.Format("delete from [{0}] where [{1}] = {2}", Tn.MailSendTemplate, CnMailSendTemplate.Id, templateId);

                    WriteDebugLog(sql);
                    using (SqlCommand sc = new SqlCommand(sql, _conn))
                    {
                        sc.ExecuteNonQuery();
                    }

                    result.Result = DM4Consts.Ok;
                    result.Message = String.Format("Mail template with id [{0}] deleted", templateId);

                    WriteLog(result.Message);

                }
                else
                {
                    result.Result = DM4Consts.ErrorUnknownEntity;
                    result.Message = String.Format("Unknown mail template with id [{0}]", templateId);
                    WriteLog(result.Message, MessageType.Error);
                }

            }

            catch (Exception ex)
            {
                WriteLog(ex.Message, MessageType.Error);
            }

            EndFunction("DeleteMailSendTemplate");
            return result;
        }
    }
}
